﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace BookClubIt.Data.Commands
{
    class BookCommandList
    {

        public static SqlCommand SaveBook
        {
            get
            {

                SqlCommand command = new SqlCommand();
                command.Parameters.Add("@Id", SqlDbType.Int);
                command.Parameters.Add("@Title", SqlDbType.VarChar, 500);
                command.Parameters.Add("@ASIN", SqlDbType.VarChar, 20);
                command.Parameters.Add("@EAN", SqlDbType.VarChar, 20);
                command.Parameters.Add("@ISBN", SqlDbType.VarChar, 20);
                command.Parameters.Add("@AmazonURL", SqlDbType.VarChar, 100);
                command.Parameters.Add("@LargeImageURL", SqlDbType.VarChar, 100);
                command.Parameters.Add("@MediumImageURL", SqlDbType.VarChar, 100);
                command.Parameters.Add("@SmallImageURL", SqlDbType.VarChar, 100);
                command.Parameters.Add("@Author", SqlDbType.VarChar, 100);
                command.Parameters.Add("@Binding", SqlDbType.VarChar, 100);
                command.Parameters.Add("@Manufacturer", SqlDbType.VarChar, 100);
                command.Parameters.Add("@Pages", SqlDbType.VarChar, 100);
                command.Parameters.Add("@PublishedDate", SqlDbType.DateTime);
                command.Parameters.Add("@Publisher", SqlDbType.VarChar, 100);
                command.Parameters.Add("@ReadingLevel", SqlDbType.VarChar, 100);
                command.Parameters.Add("@Editorial", SqlDbType.VarChar, 100);
                command.Parameters["@Id"].Direction = ParameterDirection.InputOutput;
                command.CommandType = CommandType.Text;
                command.CommandText = @"

                if(not exists(select 1 from Book where ASIN = @ASIN))
                begin
                    INSERT INTO [Book]
                           ([Title]
                           ,[ASIN]
                           ,[EAN]
                           ,[ISBN]
                           ,[Amazon_URL]
                           ,[Large_Image_URL]
                           ,[Medium_Image_URL]
                            ,[Small_Image_URL]
                            ,[Author]
                            ,[Binding]
                            ,[Manufacturer]
                            ,[Pages]
                            ,[Published_Date]
                            ,[Publisher]
                            ,[Reading_Level]
                            ,[Editorial])
                     VALUES
                           (@Title
                           ,@ASIN
                           ,@EAN
                           ,@ISBN
                           ,@AmazonURL
                           ,@LargeImageURL
                           ,@MediumImageURL
                           ,@SmallImageURL
                           ,@Author
                           ,@Binding
                           ,@Manufacturer
                           ,@Pages
                           ,@PublishedDate
                           ,@Publisher
                           ,@ReadingLevel
                           ,@Editorial)

                    set @Id = scope_identity()
                end
                else
                begin
                    UPDATE [Book]
                       SET [Title]= @Title
                           ,[ASIN] = @ASIN
                           ,[EAN]= @EAN
                           ,[ISBN]= @ISBN
                           ,[Amazon_URL]= @AmazonURL
                           ,[Large_Image_URL]= @LargeImageURL
                           ,[Medium_Image_URL]= @MediumImageURL
                            ,[Small_Image_URL]= @SmallImageURL
                            ,[Author]= @Author
                            ,[Binding]= @Binding
                            ,[Manufacturer]= @Manufacturer
                            ,[Pages]= @Pages
                            ,[Published_Date]= @PublishedDate
                            ,[Publisher]= @Publisher
                            ,[Reading_Level]= @ReadingLevel
                            ,[Editorial]= @Editorial
                     WHERE ASIN = @ASIN
                end
                ";
                return command;
            }
        }

        internal static SqlCommand GetCurrentBookId()
        {
            SqlCommand command = new SqlCommand();
            command.Parameters.Add("@Book_Club_Id", SqlDbType.Int);
            command.CommandText = @"
                    SELECT Book_Id from Book_In_Club bic  
                LEFT JOIN Book b ON bic.Book_Id = b.Id 
                where bic.Book_Club_Id = Book_Club_Id AND Is_Current = '1'
            ";
            return command;
        }

    }
}
